/*DONE* Table [dbo].[<CompanyName>$Trans_ Sales Entry$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 30.7.2020 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Trans_ Sales Entry$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Trans_ Sales Entry$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Store No_] [nvarchar](10) NOT NULL,
	[POS Terminal No_] [nvarchar](10) NOT NULL,
	[Transaction No_] [int] NOT NULL,
	[Line No_] [int] NOT NULL,
	[Receipt No_] [nvarchar](20) NOT NULL,
	[Barcode No_] [nvarchar](22) NOT NULL,
	[Item No_] [nvarchar](20) NOT NULL,
	[Sales Staff] [nvarchar](20) NOT NULL,
	[Item Category Code] [nvarchar](20) NOT NULL,
	[Retail Product Code] [nvarchar](20) NOT NULL,
	[Price] [decimal](38, 20) NOT NULL,
	[Net Price] [decimal](38, 20) NOT NULL,
	[Quantity] [decimal](38, 20) NOT NULL,
	[Price Group Code] [nvarchar](10) NOT NULL,
	[VAT Bus_ Posting Group] [nvarchar](20) NOT NULL,
	[VAT Code] [nvarchar](10) NOT NULL,
	[xTransaction Status] [int] NOT NULL,
	[Discount Amount] [decimal](38, 20) NOT NULL,
	[Cost Amount] [decimal](38, 20) NOT NULL,
	[Date] [datetime] NOT NULL,
	[Time] [datetime] NOT NULL,
	[Shift No_] [nvarchar](1) NOT NULL,
	[Shift Date] [datetime] NOT NULL,
	[Net Amount] [decimal](38, 20) NOT NULL,
	[VAT Amount] [decimal](38, 20) NOT NULL,
	[Promotion No_] [nvarchar](20) NOT NULL,
	[Standard Net Price] [decimal](38, 20) NOT NULL,
	[Disc_ Amount From Std_ Price] [decimal](38, 20) NOT NULL,
	[xStatement No_] [nvarchar](20) NOT NULL,
	[Customer No_] [nvarchar](20) NOT NULL,
	[Section] [nvarchar](10) NOT NULL,
	[Shelf] [nvarchar](10) NOT NULL,
	[Statement Code] [nvarchar](20) NOT NULL,
	[Item Disc_ Group] [nvarchar](20) NOT NULL,
	[Transaction Code] [int] NOT NULL,
	[Item Number Scanned] [tinyint] NOT NULL,
	[Keyboard Item Entry] [tinyint] NOT NULL,
	[Price in Barcode] [tinyint] NOT NULL,
	[Price Change] [tinyint] NOT NULL,
	[Weight Manually Entered] [tinyint] NOT NULL,
	[Line was Discounted] [tinyint] NOT NULL,
	[Scale Item] [tinyint] NOT NULL,
	[Weight Item] [tinyint] NOT NULL,
	[Return No Sale] [tinyint] NOT NULL,
	[Item Corrected Line] [tinyint] NOT NULL,
	[Type of Sale] [int] NOT NULL,
	[Linked No_ not Orig_] [tinyint] NOT NULL,
	[Orig_ of a Linked Item List] [tinyint] NOT NULL,
	[Staff ID] [nvarchar](20) NOT NULL,
	[Item Posting Group] [nvarchar](20) NOT NULL,
	[Total Rounded Amt_] [decimal](38, 20) NOT NULL,
	[Counter] [decimal](38, 20) NOT NULL,
	[Variant Code] [nvarchar](10) NOT NULL,
	[Serial No_] [nvarchar](50) NOT NULL,
	[Serial_Lot No_ Not Valid] [tinyint] NOT NULL,
	[Lot No_] [nvarchar](50) NOT NULL,
	[Expiration Date] [datetime] NOT NULL,
	[Member Points Type] [int] NOT NULL,
	[Member Points] [decimal](38, 20) NOT NULL,
	[Offer Blocked Points] [tinyint] NOT NULL,
	[Trans_ Date] [datetime] NOT NULL,
	[Trans_ Time] [datetime] NOT NULL,
	[Posting Exception Key] [nvarchar](75) NOT NULL,
	[Line Discount] [decimal](38, 20) NOT NULL,
	[Replicated] [tinyint] NOT NULL,
	[Customer Discount] [decimal](38, 20) NOT NULL,
	[Infocode Discount] [decimal](38, 20) NOT NULL,
	[Cust_ Invoice Discount] [decimal](38, 20) NOT NULL,
	[Unit of Measure] [nvarchar](10) NOT NULL,
	[UOM Quantity] [decimal](38, 20) NOT NULL,
	[UOM Price] [decimal](38, 20) NOT NULL,
	[Total Discount] [decimal](38, 20) NOT NULL,
	[Total Disc__] [decimal](38, 20) NOT NULL,
	[Tot_ Disc Info Line No_] [int] NOT NULL,
	[Periodic Disc_ Type] [int] NOT NULL,
	[Periodic Disc_ Group] [nvarchar](20) NOT NULL,
	[Periodic Discount] [decimal](38, 20) NOT NULL,
	[Deal Line] [tinyint] NOT NULL,
	[Deal Header Line No_] [int] NOT NULL,
	[Deal Line No_] [int] NOT NULL,
	[Deal Line Added Amt_] [decimal](38, 20) NOT NULL,
	[Deal Modifier Added Amt_] [decimal](38, 20) NOT NULL,
	[Deal Modifier Line No_] [int] NOT NULL,
	[Discount Amt_ For Printing] [decimal](38, 20) NOT NULL,
	[Coupon Discount] [decimal](38, 20) NOT NULL,
	[Coupon Amt_ For Printing] [decimal](38, 20) NOT NULL,
	[Replication Counter] [int] NOT NULL,
	[Sales Type] [nvarchar](20) NOT NULL,
	[Orig_ from Infocode] [nvarchar](20) NOT NULL,
	[Orig_ from Subcode] [nvarchar](20) NOT NULL,
	[Parent Line No_] [int] NOT NULL,
	[Infocode Entry Line No_] [int] NOT NULL,
	[Excluded BOM Line No_] [int] NOT NULL,
	[Infocode Selected Qty_] [decimal](38, 20) NOT NULL,
	[Parent Item No_] [nvarchar](20) NOT NULL,
	[Orig Trans Store] [nvarchar](10) NOT NULL,
	[Orig Trans Pos] [nvarchar](10) NOT NULL,
	[Orig Trans No_] [int] NOT NULL,
	[Orig Trans Line No_] [int] NOT NULL,
	[Refund Qty_] [decimal](38, 20) NOT NULL,
	[Refunded Line No_] [int] NOT NULL,
	[Refunded Trans_ No_] [int] NOT NULL,
	[Refunded POS No_] [nvarchar](20) NOT NULL,
	[Refunded Store No_] [nvarchar](20) NOT NULL,
	[Created by Staff ID] [nvarchar](20) NOT NULL,
	[Marked for Gift Receipt] [tinyint] NOT NULL,
	[Recommended Item] [tinyint] NOT NULL,
	[BI Timestamp] [datetime] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Tax Group Code] [nvarchar](10) NOT NULL,
	[VAT Calculation Type] [int] NOT NULL,
	[Sales Tax Rounding] [decimal](38, 20) NOT NULL,
 CONSTRAINT ['+@Company+'$Trans_ Sales Entry$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Store No_] ASC,
	[POS Terminal No_] ASC,
	[Transaction No_] ASC,
	[Line No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Trans_ Sales Entry$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END